# -*- coding: UTF-8 -*-
import pymysql
import time, datetime
import numpy as np
import traceback
import logging
import os, sys
####################################
# 数据库配置
if 1:
    DatabaseIP = '59.120.82.54'   #mg.acciot.com.tw 台湾的，正式版本
    dbport = 3306   #数据库默认端口就是3306，连接数据库时，不需要指定
    dbuser = 'wu' # user
    dbpasswd = 'Aitrip123'    # password
    database = 'wu'   # database
else:
    DatabaseIP = 'localhost'    # 测试版本
    dbport = 3306   #数据库默认端口就是3306，连接数据库时，不需要指定
    dbuser = 'wu' # user
    dbpasswd = 'Aitrip123'    # password
    database = 'wu_localhost'   # database
#######

# 时间相关的全局变量
g_PerDayWorkingTime = 24  # 每天的工作时间，单位都是小时
g_PerWeekWorkingDays = 7  # 每周有几天工作日
g_StartWorktime = '0:00'  # 机台开始时间
g_EndWorktime = '24:00'  # 机台工作结束时间
# 机台是24小时运行，中间没有休息
g_MorningStartWorktime = '0:00'  # 上午工作开始时间
g_MorningEndWorktime = '12:00'  # 上午工作结束时间
g_AfternoonStartWorktime = '12:00'  # 下午工作开始时间
g_AfternoonEndWorktime = '23:59'  # 下午工作结束时间，实际上是17:30下班，如果开始时间在17:00到17:30，再安排计划无意义，要安排到下一天
g_NoonRestTime = 0.0  # 中午休息的时间段：0.0小时
g_NightRestTime = 0  # 晚上下班到第二天上午上班的时间段：0小时
# 制程相关的全局变量
g_MaxProcedure_num = 12  # 每个工单里最大的制程数是12
g_MaxTimeBetweenTwoProcedures = 1 # 2个不同规格工序的切换，要调整的时间，默认1小时

g_DeviceTypeID2ab_disc = {}  # a/b的值    对应每个devicetypeID的阀值
g_AllNeedPlanProcedureID2manhours_dict = {}    # 所有参与排程的procedure工序的字典，key=procedureID value=procedureName
g_AllJT2ProcedureName_dict = {}        # 所有参与排程的procedure工序的字典，key=procedureID value=procedureType
g_AllValidJT_ID2ProceduretypeID_dict = {}       # 所有可以排程的机台字典，key=jtID, valud=proceduretype_id
g_AllNeedPlanProcedureAndJTInfos_tuple = ()             # 所有要排程的工序和机台等的相关信息
####################################

g_countTest = 0  # 测试变量
g_countTest1 = 0  # 测试变量

# 分批上传数据库
def batchUploadtoDB(sql, mylist, oneUploadNum = 50):
    if len(mylist) == 0:
        return False

    conn = pymysql.Connect(
        host=DatabaseIP,
        port=dbport,
        user=dbuser,
        passwd=dbpasswd,
        db=database,
        charset='utf8',
        local_infile=1
    )
    # 使用cursor()方法获取操作游标
    cursor = conn.cursor()

    toList = []
    for i in range(1, len(mylist)+1):
        if i % oneUploadNum == 0:   # 累积到数量了，要上传
            try:
                (ret, conn, cursor) = _reConn(conn, cursor)
                if (ret == False):
                    return False
                result = cursor.executemany(sql, toList)
                toList = []  # 列表初始化
            except:
                return False
        toList.append(mylist[i-1])
    # 把余下来的数据也上传
    if len(toList) > 0:
        try:
            (ret, conn, cursor) = _reConn(conn, cursor)
            if (ret == False):
                return False
            result = cursor.executemany(sql, toList)
        except:
            return False
    # 数据库关闭
    conn.commit()
    cursor.close()
    conn.close()
    return True

def _conn(conn, cursor):
    try:
        conn = pymysql.Connect(
            host=DatabaseIP,
            port=dbport,
            user=dbuser,
            passwd=dbpasswd,
            db=database,
            charset='utf8',
            local_infile=1
        )
        cursor = conn.cursor()
        cursor.execute('SET SESSION WAIT_TIMEOUT = 86400')

        return (True, conn, cursor)
    except:
        return (False, conn, cursor)

def _reConn(conn, cursor, num=3, stime=1):  # 重试连接总次数为1天,这里根据实际情况自己设置,如果服务器宕机1小时都没发现就......
    _number = 0
    _status = True
    ret = True
    while _status and _number <= num:
        try:
            try:
                conn.ping()  # cping 校验连接是否异常
            except:
                conn.ping(True) # 在使用ping()的时候，可能由于版本问题出现ping()函数需要参数，从而发生异常
            _status = False
        except:
            (ret, conn, cursor) = _conn(conn, cursor)
            if ret == True:  # 重新连接,成功退出
                _status = False
                break
            _number += 1
            time.sleep(stime)  # 连接不成功,休眠3秒钟,继续循环，知道成功或重试次数结束
    if(_number > num):
        # False
        return (False, conn, cursor)

    return (True, conn, cursor)

####################################
# 1.把datetime转成字符串
# 2.只到分
def datetime_toString(dt):
    # print("1.把datetime转成字符串: ", dt.strftime("%Y-%m-%d %H:%M"))
    if isinstance(dt, datetime.date):
        if dt != None:
            return dt.strftime("%Y-%m-%d %H:%M")

    return ("0000-00-00 00:00")

    #return dt.strftime("%m月%d日")

# 2.把字符串转成datetime
def string_toDatetime(st):
    if isinstance(st, unicode):
        st = st.encode("gbk")
    if isinstance(st, str):
        if st != "0000-00-00 00:00:00" and st != "0000-00-00 00:00" and st is not None:
            if len(st) > 16:
                return datetime.datetime.strptime(st, "%Y-%m-%d %H:%M:%S")
            else:
                return datetime.datetime.strptime(st, "%Y-%m-%d %H:%M")
    else:
        return st   # 不处理
    return None

# 把时间戳转化为时间: 1479264792 to 2016-11-16 10:53:12
def TimeStampToTime(timestamp):
    timeStruct = time.localtime(timestamp)
    return time.strftime('%Y-%m-%d %W %H:%M', timeStruct)

# 3.把字符串转成时间戳形式
def string_toTimestamp(st):
    # print("3.把字符串转成时间戳形式:", time.mktime(time.strptime(st, "%Y-%m-%d %H:%M:%S")))
    return time.mktime(time.strptime(st, "%Y-%m-%d %H:%M"))
    #return time.mktime(time.strptime(st, "%Y-%m-%d"))

# 4.把时间戳转成字符串形式
def timestamp_toString(sp):
    # print("4.把时间戳转成字符串形式: ", time.strftime("%Y-%m-%d %H:%M", time.localtime(sp)))
    return time.strftime("%Y-%m-%d %H:%M", time.localtime(sp))

# 5.把datetime类型转外时间戳形式
def datetime_toTimestamp(dt):
    # print("5.把datetime类型转外时间戳形式:", time.mktime(dt.timetuple()))
    return time.mktime(dt.timetuple())

# 计算开始和结束日期内的工作时长，单位是小时
def calulateTimeSpawHours(beginDatetime, endDatetime, TimeCursor):
    if endDatetime <= beginDatetime:
        return (0.0, endDatetime)
    # 组合工作日的中午和下午的开始和结束时间点
    (BeginDayMorning_StartTime, BeginDayMorning_EndTime, BeginDayAfternoon_StartTime, BeginDayAfternoon_EndTime, NextDayMorning_StartTime) = \
        composeMonrningAndAfternoon_BeginAndEndtime(TimeCursor)
    # 判断该时间段内（beginDatetime, endDatetime），在该天（CurrentDay）内的时间长度
    # 第1种可能，开始时间点，比当天上午的早上时间还要早
    if beginDatetime < BeginDayMorning_StartTime:
        # 理论上不可能
        if endDatetime <= BeginDayMorning_StartTime:
            return (0.0, BeginDayMorning_StartTime)
        # 结束时间点，在当天的中午时间段
        elif endDatetime > BeginDayMorning_StartTime and endDatetime <= BeginDayMorning_EndTime:
            return ((endDatetime - BeginDayMorning_StartTime).seconds / 3600.0, endDatetime)    # 转换为小时
        # 结束时间点，在当天的中午休息时间段
        elif endDatetime > BeginDayMorning_EndTime and endDatetime <= BeginDayAfternoon_StartTime:
            return ((BeginDayMorning_EndTime - BeginDayMorning_StartTime).seconds / 3600.0, BeginDayAfternoon_StartTime)    # 转换为小时，就是一个上午的时间
        # 结束时间点，在当天的下午工作时间段
        elif endDatetime > BeginDayAfternoon_StartTime and endDatetime <= BeginDayAfternoon_EndTime:
            return (((BeginDayMorning_EndTime - BeginDayMorning_StartTime).seconds +
                    (endDatetime - BeginDayAfternoon_StartTime).seconds) / 3600.0, endDatetime)    # 转换为小时，就是一个上午的时间加上下午的工作时间段
        else:   # 就是一整天
            # 下一天的开始时间
            return (g_PerDayWorkingTime, NextDayMorning_StartTime)
    # 第2种可能，开始时间点，在当天的中午工作时间段
    elif beginDatetime >= BeginDayMorning_StartTime and beginDatetime <= BeginDayMorning_EndTime:
        # 结束时间点也在上午工作时间段
        if endDatetime > BeginDayMorning_StartTime and endDatetime <= BeginDayMorning_EndTime:
            return ((endDatetime - beginDatetime).seconds / 3600.0, endDatetime)   # 转换为小时
        # 结束时间点，在当天的中午休息时间段
        elif endDatetime > BeginDayMorning_EndTime and endDatetime < BeginDayAfternoon_StartTime:
            return ((BeginDayMorning_EndTime - beginDatetime).seconds / 3600.0, BeginDayAfternoon_StartTime)    # 转换为小时，就是一个上午的时间
        # 结束时间点，在当天的下午工作时间段
        elif endDatetime >= BeginDayAfternoon_StartTime and endDatetime <= BeginDayAfternoon_EndTime:
            return (((BeginDayMorning_EndTime - beginDatetime).seconds +
                    (endDatetime - BeginDayAfternoon_StartTime).seconds) / 3600.0, endDatetime)    # 转换为小时，就是一个上午的时间加上下午的工作时间段
        else:   # 就是整个一个下午和上午的工作时间段
            # 下一天的开始时间
            return (((BeginDayMorning_EndTime - beginDatetime).seconds +
                    (BeginDayAfternoon_EndTime - BeginDayAfternoon_StartTime).seconds) / 3600.0, NextDayMorning_StartTime)    # 转换为小时，就是一个上午的时间加上下午的工作时间段
    # 第3种可能，开始时间点，在当天的中午休息时间段
    elif beginDatetime > BeginDayMorning_EndTime and beginDatetime < BeginDayAfternoon_StartTime:
        # 结束时间点也在中午休息时间段
        if endDatetime > BeginDayMorning_EndTime and endDatetime < BeginDayAfternoon_StartTime:
            return (0.0, BeginDayAfternoon_StartTime)
        # 结束时间点，在当天的下午工作时间段
        elif endDatetime >= BeginDayAfternoon_StartTime and endDatetime <= BeginDayAfternoon_EndTime:
            return ((endDatetime - BeginDayAfternoon_StartTime).seconds / 3600.0, endDatetime)  # 就是下午的工作时间段
        else:  # 就是整个一个下午和上午的工作时间段
            # 下一天的开始时间
            return ((BeginDayAfternoon_EndTime - BeginDayAfternoon_StartTime).seconds / 3600.0, NextDayMorning_StartTime)  # 就是一整个下午的工作时间段
    # 第4种可能，开始时间点，在当天的下午工作时间段
    elif beginDatetime >= BeginDayAfternoon_StartTime and beginDatetime <= BeginDayAfternoon_EndTime:
        # 结束时间点，在当天的下午工作时间段
        if endDatetime >= BeginDayAfternoon_StartTime and endDatetime <= BeginDayAfternoon_EndTime:
            return ((endDatetime - beginDatetime).seconds / 3600.0, endDatetime)  # 就是下午的工作时间段
        else:  # 就是整个一个下午和上午的工作时间段
            # 下一天的开始时间
            return ((BeginDayAfternoon_EndTime - beginDatetime).seconds / 3600.0, NextDayMorning_StartTime)  # 就是一整个下午的工作时间段
    # 第5种可能，开始时间点，在当天的下班时间后
    else:
        # 下一天的开始时间
        return (0.0, NextDayMorning_StartTime)

# 组合输入日期的的中午和下午的开始和结束时间
def composeMonrningAndAfternoon_BeginAndEndtime(Currentday):
    # 输入日期的上午工作时间段
    BeginDayMorning_StartTime = datetime.datetime.strptime(str(Currentday.date()) + g_MorningStartWorktime,'%Y-%m-%d%H:%M')
    BeginDayMorning_EndTime = datetime.datetime.strptime(str(Currentday.date()) + g_MorningEndWorktime,'%Y-%m-%d%H:%M')
    # 输入日期的下午工作时间段
    BeginDayAfternoon_StartTime = datetime.datetime.strptime(str(Currentday.date()) + g_AfternoonStartWorktime,'%Y-%m-%d%H:%M')
    BeginDayAfternoon_EndTime = datetime.datetime.strptime(str(Currentday.date()) + g_AfternoonEndWorktime,'%Y-%m-%d%H:%M')

    # 下一天的开始时间
    delta = datetime.timedelta(days=1)
    NextDayMorning_StartTime = BeginDayMorning_StartTime
    NextDayMorning_StartTime += delta

    return (BeginDayMorning_StartTime, BeginDayMorning_EndTime, BeginDayAfternoon_StartTime, BeginDayAfternoon_EndTime,
            NextDayMorning_StartTime)

# 判断一个时间段内跨越几周
def CountOverWeeks(BeginDate, EndDate):
    nWeeks = 0
    VarDate = BeginDate
    for i in range((EndDate-BeginDate).days):
        whatday = VarDate.weekday()
        if whatday == 5: # 周六
            nWeeks += 1
        delta = datetime.timedelta(days=1)
        VarDate += delta

    return nWeeks

# 调整日期时间从休息日调整到工作日时间内
# 输入参数：InputStarttime为datetime格式
# 输入参数：AdjustDirection为正负，代表加减
def AdjustRestToWorkdatetime(InputStarttime, AdjustDirection):
    # 取结束日期是星期几？
    # 0: '星期一' ... 4: '星期五', 5: '星期六', 6: '星期天'
    nWeekDay_End = InputStarttime.weekday()
    if nWeekDay_End > 4:  # 在周末
        # 再加上周末的天数
        if AdjustDirection > 0: # 向前调整为下周一
            delta = datetime.timedelta(days=7 - nWeekDay_End)
        else:   # 向前调整为上周五
            delta = datetime.timedelta(days=nWeekDay_End - 4)
            delta *= -1
        InputStarttime += delta
    return InputStarttime

# 函数功能：调整输入的datetime时间为工作时间，如果InputDelta为0，代表是开始的时间点，要调整为开始时间
# 要求如下：
# 1、输入的开始时间点一定要在工作时间段，因为每个时间点都通过了该函数，故该点已经满足
# 2、输入参数Input_StartDateTime是datetime格式
# 3、输入参数InputDelta是时间段，float格式，单位是小时
# 4、输入参数manID是对某个机台的时间做调整，如果是0对所有机台都有效
def AdjustToWorkingHours(Input_StartDateTime, InputDelta, manID):
    # 因为机台不休，故没有休息日
    if InputDelta <= 0:
        return Input_StartDateTime

    delta = datetime.timedelta(hours=InputDelta)
    Input_StartDateTime += delta
    # 最后返回调整后的时间
    return Input_StartDateTime

# 列表排序，结束的时间更重要，要排在开始时间的前面，并取得当前与下个工作空闲的时间段
def GetSortedAllJTWorkingArrayAndToNextWorkFreetime(AllManWorkingArray):
    # 列表排序，结束的时间更重要，要排在开始时间的前面
    AllManWorkingArray = sorted(AllManWorkingArray, key=lambda x: (x[0], x[2], x[1], x[3]))
    # 并把其他距离下个工作时段的空闲时长计算出来，因为已经按manid、结束时间和开始时间排好序了，故如果是同一个机台可以用后项减去前项
    i = 1   # 从1开始
    while i < len(AllManWorkingArray):
        if AllManWorkingArray[i - 1][0] == AllManWorkingArray[i][0]:  # 是同一个机台，且不是追加的机台
            # 更新前项的空闲时间段：后项的开始时间段 - 前项的结束时间段 单位是小时
            pre2nexttime_delta = (datetime_toTimestamp(AllManWorkingArray[i][1]) - \
                                            datetime_toTimestamp(AllManWorkingArray[i - 1][2])) / 3600.0
            # 合并1：俩段时间相连了，合并到前一项，并删除后项
            if pre2nexttime_delta <= 0.033:   # 小于2分钟，就合并
                AllManWorkingArray[i - 1][2] = AllManWorkingArray[i][2]
                AllManWorkingArray[i - 1][3] = AllManWorkingArray[i][3]
                AllManWorkingArray[i - 1][4] = AllManWorkingArray[i][4]
                del AllManWorkingArray[i]
                continue
        # 自变量增加
        i += 1

    # 有更新，列表排序，结束的时间更重要，要排在开始时间的前面，最晚结束的时间排在前面，如果中间没有空闲的时间，就按最晚空闲的时间算
    AllManWorkingArray = sorted(AllManWorkingArray, key=lambda x: (x[0], x[2], x[1], x[3]), reverse=True)
    return (AllManWorkingArray)

# AllPlanlogResult：
# 0: PL.`id`, 1:PL.`workorder_id`, 2:WO.`productspec_id`, 3:WO.`name`, 4:WO.`num`, 5:PL.`finishnum`, 6:PL.`maxnum`,
# 7：PL.`yjkg`, 8：WO.`ordernumber`" \
# 9 ~ 20: PL.`procedure1_man_id` ~ PL.`procedure12_man_id`
# 21 ~ 32: PL.`procedure1_startdate` ~ PL.`procedure12_startdate`
# 33 ~ 44：PL.`procedure1_finishdate` ~ PL.`procedure12_finishdate`
# 45 ~ 56：PL.`procedure1_manhours` ~ PL.`procedure12_manhours`
# 57 ~ 68：PL.`procedure1_id` ~ PL.`procedure12_id`
# 69 ~ 80：PL.`procedure1_mannum` ~ PL.`procedure12_mannum`

# 取得所有机台的工作时间表：[0]JTID, [1]开始时间，[2]结束时间, [3]距离下一个时间空闲时间
#（如果是[3]=-1就是最后一个）
def GetAllJTWorkingInfoTable(startTime, AllPlanlogResult):
    try:
        # 1、先取得所有制程对应机台的对应表
        OneJTWorking = []
        AllJTWorking_list = []
        kk = 0
        for row_1 in AllPlanlogResult:
            # 5:PL.`finishnum` 6:PL.`maxnum`
            for i in range(row_1[5], row_1[6]):
                # 还要判断这个机台是不是要检修了?如果不在检修，就可以排程
                # 9: PL.`procedure1_man_id` manID对应JT_ID
                if(not g_AllValidJT_ID2ProceduretypeID_dict.has_key(row_1[9 + i])):
                    continue
                # 5:PL.`finishnum`, 57：PL.`procedure1_id` 21: PL.`procedure1_startdate` 33: PL.`procedure1_finishdate`
                if( g_AllNeedPlanProcedureID2manhours_dict.has_key(row_1[57 + i]) and startTime >= row_1[21 + i] and startTime <= row_1[33 + i]):
                    # 这个工序当前正在做，要追加到机台工作列表
                    OneJTWorking = [row_1[9 + i], row_1[21 + i], row_1[33 + i], -1, row_1[2]]
                    # 0:jt_ID, 1: starttime, 2:endtime，3，空闲时间(-1代表后面都空闲),
                    # 4，主产品ID（用于判断要不要调整参数时间，如果为0，所有都有效，如果是-1是初始值，只是占位，不考虑）
                    AllJTWorking_list.append(OneJTWorking)
                if row_1[21 + i] > startTime:   # 后面的工序时间已经是将来要做的，需要重新排程
                    break
        # 变成NP表，以便去重
        npArr = np.array(AllJTWorking_list)
        # 去掉重复的行
        mylist = np.array(list(set([tuple(t) for t in npArr])))
        # 转成列表
        AllJTWorking_list = mylist.tolist()

        # 将其他的剩余机台也加入，只是工作时长为-1，代表后面时间完全空闲，占位符
        for key in g_AllValidJT_ID2ProceduretypeID_dict.keys():
            bExist = False
            for row1 in AllJTWorking_list:
                if key == row1[0]:  # 已经有该机台存在
                    bExist = True
                    break
            if not bExist:
                OneJTWorking = [key, startTime, startTime, -1, -1]
                AllJTWorking_list.append(OneJTWorking)

        # 将工序与机台ID排序，先结束的排前面，准备安排
        AllJTWorking_list = sorted(AllJTWorking_list, key=lambda x: (x[0], x[2]))
        AllJTWorking_list = GetSortedAllJTWorkingArrayAndToNextWorkFreetime(AllJTWorking_list)

        # 将工序与机台ID排序，先结束的排前面，准备安排
        AllJTWorking_list = sorted(AllJTWorking_list, key=lambda x: (x[0], x[2]))

        # 返回
        return (AllJTWorking_list)
    except Exception as e:
        # 访问异常的错误编号和详细信息
        print(e.args)
        print(str(e))
        print(repr(e))
        print "database connect error."

# 更新机台的用时表，为了填写数据库fa_wu_jt的usehours字段
# AllJTWorking_list必须是包含完整的JT
# 要注意用时要减掉机台在处理不同规格的工序时，已经累加的调整时间
def updateJTUsemanhours(AllJTWorking_list):
    if len(AllJTWorking_list) <= 0:
        return AllJTWorking_list
    OneUpdateJTUsehours = [0.0, 0]  # usehours和jt_ID
    AllUpdateJTUsehours = []
    # 要先将所有的JT添加到该表
    bExist = False
    for row in AllJTWorking_list:
        for row_inner in AllUpdateJTUsehours:
            if row[0] == row[1]:
                bExist = True
                break
        if not bExist:
            OneUpdateJTUsehours = [0.0, row[0]]
            AllUpdateJTUsehours.append(OneUpdateJTUsehours)

    for i in range(len(AllJTWorking_list)):
        for row_inner in AllUpdateJTUsehours:
            if AllJTWorking_list[i][0] == row_inner[1]:
                timediff = (AllJTWorking_list[i][2] - AllJTWorking_list[i][1]).seconds / 3600
                if i != 0 and AllJTWorking_list[i][4] != AllJTWorking_list[i-1][4]:
                    # 容错
                    if timediff > g_MaxTimeBetweenTwoProcedures:
                        timediff -= g_MaxTimeBetweenTwoProcedures
                row_inner[0] += timediff

    return AllUpdateJTUsehours

# 判断当前的工序添加后，是否需要加上调整参数时间，如果前一个工序与当前工序的产品不是同一规格，就要加上
def isNeedAddAdjustTime(AllManWorkingArray, jtID, beginTime, currentProductspec_ID):
    # AllManWorkingArray_O机台工作记录表按相同JTID、结束时间、开始时间升序、工序类型排列，越早结束越可能被先安排
    AllManWorkingArraySorted = sorted(AllManWorkingArray, key=lambda x: (x[0], x[2]))
    bNeed = False
    preProductID = -1
    # AllManWorkingArray: [0]JTID, [1]开始时间，[2]结束时间, [3]距离下一个时间空闲时间 [4] product_ID
    iPlanNum = 0   # 记录有几个在该机台准备做
    for row in AllManWorkingArraySorted:
        if(jtID == row[0]): # jt_id同
            iPlanNum += 1
            if(beginTime >= row[2]):
                preProductID = row[4]
        elif(preProductID > 0): # 已经按jt_ID排序了，如果不等，且已经有相等赋值，说明已经比较完了，后面无需再比较
            break

    if(iPlanNum > 1 and currentProductspec_ID != preProductID): # 第一个不需要调整
        bNeed = True

    return bNeed

######################################
# 挑出由谁干，从什么时间开始干
# 考虑最早结束，就安排
# 当要安排的时间相同，按能力值大安排
# 返回值：(manID, CurrentProcedureX_Startdatetime, AllManWorkingArray_O)
def WhoFromWhenDo_byTime(procedureid, startTime, manHours, AllManWorkingArray):
    # 先计算该制程的预计结束时间
    returnStartDatetime = AdjustToWorkingHours(startTime, 0, 0)
    if(manHours == 0.0):
        # 该工时不需要排程，开始和结束时间是同一个
        returnStartDatetime = AdjustToWorkingHours(returnStartDatetime, 0, 0)
        return (0, returnStartDatetime, returnStartDatetime, AllManWorkingArray, -1)

    # AllManWorkingArray_O机台工作记录表按相同JTID、结束时间、开始时间升序、工序类型排列，越早结束越可能被先安排
    AllManWorkingArray = sorted(AllManWorkingArray, key=lambda x: (x[0], x[2], x[1]))

    # 先考虑中间空闲的时间段
    manID = 0  # 初始化一个机台ID为0
    bNeedAddAdjustTime = False  # 是否需要加上调整参数时间，如果前一个工序与当前工序的产品不是同一规格，就要加上
    currentProductspec_ID = 0   # 决定要不要增加调整机台参数的时间？
    # 找所有能做该procedure的机台
    # [0]procedure_id, [1]JT_id, [2]productspec_id [3]proceduretype_id [4]procedurenumber [5] procedure_name
    # [6]procedure_manhours [7]procedure_order [8] procedure_switch [9]jt_name [10]jt_state [11] jt_usehours
    # 因为相同工序如果有多个机台可以做，就有多笔记录，所以只要轮询，就可以找到多个机台，做对比，选择合适的机台做
    for row_firstloop in g_AllNeedPlanProcedureAndJTInfos_tuple:    # g_AllNeedPlanProcedureAndJTInfos_tuple是按procedureID排序的
        # 先通过工序ID找机台
        if procedureid == row_firstloop[0]:     # procedure_id工序相同
            # 工序名称procedure_name同，对应机台IT，再判断是否要安排该机台
            # AllManWorkingArray: [0]JTID, [1]开始时间，[2]结束时间, [3]距离下一个时间空闲时间 [4] product_ID
            for i in range(0, len(AllManWorkingArray)):
                # 通过JTID确定位置
                if row_firstloop[1] == AllManWorkingArray[i][0]:   # JT_ID同
                    # AllManWorkingArray[i][3]==-1，代表机台的最后完成的时间，因为已经排过序了
                    if AllManWorkingArray[i][3] == -1: # 该机台后面都空闲
                        if manID == 0:  # 没有可以比较的，直接赋值
                            manID = AllManWorkingArray[i][0]
                            currentProductspec_ID = row_firstloop[2]    # 决定要不要增加调整机台参数的时间？
                            # AllManWorkingArray_O机台工作记录表已经按结束时间升序排列，越早结束越可能被先安排
                            if (AllManWorkingArray[i][2] > returnStartDatetime):
                                returnStartDatetime = AllManWorkingArray[i][2]
                            # 判断是否要加调整的时间
                            bNeedAddAdjustTime = isNeedAddAdjustTime(AllManWorkingArray, manID, returnStartDatetime, row_firstloop[2])
                            continue
                        else:   # 就要比较，安排哪个机台合理？
                            # 先判断是不是同一个产品，要不要调整参数？
                            bTempNeedAdjustTime = isNeedAddAdjustTime(AllManWorkingArray,
                                                                  AllManWorkingArray[i][0],
                                                                  AllManWorkingArray[i][2],
                                                                  row_firstloop[2])
                            # 第一种情况
                            if(AllManWorkingArray[i][2] > returnStartDatetime): # 要安排的时间比已经安排的时间还要晚
                                # 只判断结束时间看哪个早？谁早结束就安排谁
                                if (bTempNeedAdjustTime): tempManHours = manHours + g_MaxTimeBetweenTwoProcedures
                                # 分别计算2种安排的结束时间，哪个先结束，就安排哪个
                                tempFinishdatetimeNew = AdjustToWorkingHours(AllManWorkingArray[i][2], tempManHours, manID)  # 用最初的时间
                                if bNeedAddAdjustTime:
                                    tempFinishdatetimeOld = AdjustToWorkingHours(returnStartDatetime,
                                                                                 tempManHours,
                                                                                 manID)  # 用最初的时间
                                else:
                                    tempFinishdatetimeOld = AdjustToWorkingHours(returnStartDatetime, manHours,
                                                                                 manID)  # 用最初的时间
                                if tempFinishdatetimeNew < tempFinishdatetimeOld:
                                    # 新的先结束，就要换
                                    # 再判断时间片是否满足，因为是当前是-1，后面都空闲，不用判断
                                    manID = AllManWorkingArray[i][0]
                                    currentProductspec_ID = row_firstloop[2]  # 决定要不要增加调整机台参数的时间？
                                    returnStartDatetime = AllManWorkingArray[i][2]
                                    # 判断是否要加调整的时间
                                    bNeedAddAdjustTime = bTempNeedAdjustTime
                                    continue
                                continue
                            elif(AllManWorkingArray[i][2] == returnStartDatetime):
                                # 判断结束时间，哪个可以先结束，就用哪个？
                                if bNeedAddAdjustTime == bTempNeedAdjustTime:
                                    # 也一样，都需要调整或不调整，就不变
                                    continue
                                elif bNeedAddAdjustTime and not bTempNeedAdjustTime:
                                    # 原来的需要调整，加调整参数时间，而新的不需要，那肯定就会提前结束，采用新的
                                    # 再判断时间片是否满足，因为是当前是-1，后面都空闲，不用判断
                                    manID = AllManWorkingArray[i][0]
                                    currentProductspec_ID = row_firstloop[2]  # 决定要不要增加调整机台参数的时间？
                                    returnStartDatetime = AllManWorkingArray[i][2]
                                    # 判断是否要加调整的时间
                                    bNeedAddAdjustTime = isNeedAddAdjustTime(AllManWorkingArray, manID,
                                                                             returnStartDatetime, row_firstloop[2])
                                    continue
                                else:   # 原来的可以提前结束，不需要替换
                                    pass
                            else:   # AllManWorkingArray[i][2] < returnStartDatetime
                                # 判断结束时间，哪个可以先结束，就用哪个？
                                if bNeedAddAdjustTime ^ bTempNeedAdjustTime:    # 异或后，2者不同
                                    if (bTempNeedAdjustTime): tempManHours = manHours + g_MaxTimeBetweenTwoProcedures
                                    # 分别计算2种安排的结束时间，哪个先结束，就安排哪个
                                    tempFinishdatetimeNew = AdjustToWorkingHours(startTime, tempManHours, manID)   # 用最初的时间
                                    if bNeedAddAdjustTime:
                                        tempFinishdatetimeOld = AdjustToWorkingHours(returnStartDatetime, tempManHours,
                                                                              manID)  # 用最初的时间
                                    else:
                                        tempFinishdatetimeOld = AdjustToWorkingHours(returnStartDatetime, manHours,
                                                                              manID)  # 用最初的时间
                                    if tempFinishdatetimeNew < tempFinishdatetimeOld:
                                        # 新的先结束，就要换
                                        # 再判断时间片是否满足，因为是当前是-1，后面都空闲，不用判断
                                        manID = AllManWorkingArray[i][0]
                                        currentProductspec_ID = row_firstloop[2]  # 决定要不要增加调整机台参数的时间？
                                        returnStartDatetime = AllManWorkingArray[i][2]
                                        # 判断是否要加调整的时间
                                        bNeedAddAdjustTime = bTempNeedAdjustTime
                                        continue
                                else:   # 2者相同，肯定要更新
                                    manID = AllManWorkingArray[i][0]
                                    currentProductspec_ID = row_firstloop[2]  # 决定要不要增加调整机台参数的时间？
                                    returnStartDatetime = AllManWorkingArray[i][2]
                                    # 判断是否要加调整的时间
                                    bNeedAddAdjustTime = bTempNeedAdjustTime
                                    continue
                    # AllManWorkingArray[i][3]!= -1，该机台有碎片时间段，要判断是否大于需要的时间
                    elif AllManWorkingArray[i][3] >= manHours:
                        # 判断是否要加调整的时间
                        bTempNeedAdjustTime = isNeedAddAdjustTime(AllManWorkingArray,
                                                                  AllManWorkingArray[i][0],
                                                                  AllManWorkingArray[i][2],
                                                                  row_firstloop[2])
                        if bNeedAddAdjustTime and AllManWorkingArray[i][3] <= manHours + g_MaxTimeBetweenTwoProcedures:
                            # 调整时间后，已经不满足了，就直接返回
                            continue
                        # 统一计算下
                        # 分别计算2种安排的结束时间，哪个先结束，就安排哪个
                        # 新的结束时间
                        tempManHours = (manHours + g_MaxTimeBetweenTwoProcedures) if bTempNeedAdjustTime else manHours
                        tempFinishdatetimeNew = AdjustToWorkingHours(AllManWorkingArray[i][2], tempManHours, manID)  # 用最初的时间
                        # 就的结束时间
                        tempOldManHours = (manHours + g_MaxTimeBetweenTwoProcedures) if bNeedAddAdjustTime else manHours
                        tempFinishdatetimeOld = AdjustToWorkingHours(startTime, tempOldManHours, manID)  # 用最初的时间

                        # 如果没有，先初始化
                        if manID == 0:  # 没有可以比较的，直接赋值
                            # 判断有没有超过下一个时间段开始点AllManWorkingArray[i + 1][1]？
                            if (AllManWorkingArray[i][2] < returnStartDatetime):
                                if (AdjustToWorkingHours(returnStartDatetime, tempManHours, manID)) < AllManWorkingArray[i + 1][1]:
                                    manID = AllManWorkingArray[i][0]
                                    currentProductspec_ID = row_firstloop[2]    # 决定要不要增加调整机台参数的时间？
                                    # AllManWorkingArray_O机台工作记录表已经按结束时间升序排列，越早结束越可能被先安排
                                    returnStartDatetime = returnStartDatetime   # 时间一样
                                    # 判断是否要加调整的时间
                                    bNeedAddAdjustTime = bTempNeedAdjustTime
                                    continue
                            else:   # 大于returnStartDatetime
                                if tempFinishdatetimeNew < AllManWorkingArray[i + 1][1]:    # 没超过
                                    manID = AllManWorkingArray[i][0]
                                    currentProductspec_ID = row_firstloop[2]    # 决定要不要增加调整机台参数的时间？
                                    # AllManWorkingArray_O机台工作记录表已经按结束时间升序排列，越早结束越可能被先安排
                                    returnStartDatetime = AllManWorkingArray[i][2]
                                    # 判断是否要加调整的时间
                                    bNeedAddAdjustTime = bTempNeedAdjustTime
                                    continue
                        # manID 不为0
                        # 第一种情况
                        if (AllManWorkingArray[i][2] >= returnStartDatetime):  # 要安排的时间比已经安排的时间还要晚
                            # 新的时间不可能超过下一个时间点，前面已经判断过了
                            # 只判断结束时间看哪个早？谁早结束就安排谁
                            if tempFinishdatetimeNew < tempFinishdatetimeOld:
                                # 新的先结束，就要换
                                manID = AllManWorkingArray[i][0]
                                currentProductspec_ID = row_firstloop[2]  # 决定要不要增加调整机台参数的时间？
                                returnStartDatetime = AllManWorkingArray[i][2]
                                # 判断是否要加调整的时间
                                bNeedAddAdjustTime = bTempNeedAdjustTime
                            continue
                        else:  # AllManWorkingArray[i][2] < returnStartDatetime
                            # 只判断结束时间看哪个早？谁早结束就安排谁
                            if tempFinishdatetimeNew < tempFinishdatetimeOld:
                                # 新的先结束，就要换
                                manID = AllManWorkingArray[i][0]
                                currentProductspec_ID = row_firstloop[2]  # 决定要不要增加调整机台参数的时间？
                                returnStartDatetime = AllManWorkingArray[i][2]
                                # 判断是否要加调整的时间
                                bNeedAddAdjustTime = bTempNeedAdjustTime
                            continue

        if manID != 0 and procedureid != row_firstloop[0]:
            # 已经排过程，因为g_AllNeedPlanProcedureAndJTInfos_tuple是按procedureid排序的，
            # 相同的procedure_id在一起，如果已经找到过，现在又不同，说明后面肯定没有了，直接跳出
            break


    # 已经找到了，返回
    if manID <> 0:
        # 机台工作记录表恢复顺序，按结束时间降序排列
        AllManWorkingArray = sorted(AllManWorkingArray, key=lambda x: (x[0], x[2], x[1]), reverse=True)
        # 计算当前制程结束的时间
        tempManHours = (manHours + g_MaxTimeBetweenTwoProcedures) if bNeedAddAdjustTime else manHours
        Finishdatetime = AdjustToWorkingHours(returnStartDatetime, tempManHours, manID)
        return (manID, returnStartDatetime, Finishdatetime, AllManWorkingArray, currentProductspec_ID)
    else:
        print "ERROR！！！ manID ==0, DevicetypeID = " , procedureid
        return (0, startTime, startTime, AllManWorkingArray, currentProductspec_ID)

# 以下处理，要得到每个工单内各个制程的开始时间，填写fa_wu_planlog表的procedure1_startdate~ procedure8_startdate，
# procedure1_finishdate~ procedure8_finishdate，procedure1_man_id~ procedure8_man_id
# 按最迟开始的时间作为最外面的循环
# 1、《《《AllPlanlogResult》》》表，是planlog和workorder表合成出来的列表
# 0: PL.`id`, 1:PL.`workorder_id`, 2:WO.`productspec_id`, 3:WO.`name`, 4:WO.`num`, 5:PL.`finishnum`, 6:PL.`maxnum`,
# 7：PL.`yjkg`, 8：WO.`ordernumber`" \
# 9 ~ 20: PL.`procedure1_man_id` ~ PL.`procedure12_man_id`
# 21 ~ 32: PL.`procedure1_startdate` ~ PL.`procedure12_startdate`
# 33 ~ 44：PL.`procedure1_finishdate` ~ PL.`procedure12_finishdate`
# 45 ~ 56：PL.`procedure1_manhours` ~ PL.`procedure12_manhours`
# 57 ~ 68：PL.`procedure1_id` ~ PL.`procedure12_id`
# 69 ~ 80：PL.`procedure1_mannum` ~ PL.`procedure12_mannum`
# 2、《《《AllManWorkingArray_O》》》列表元素，最终都要填这个表，对所有机台的工作时间段记录，包含休假也认为是不能被安排的时间段

# 3、《《《AllUpdatePlanlogList》》》列表元素，用于更新数据库fa_wu_planlog/planlog0
# 【0】procedure1_man_id,【1】procedure1_startdate ,【2】procedure11_finishdate
# 【3】procedure2_man_id,【4】procedure2_startdate ,【5】procedure2_finishdate
# ...
# 【33】procedure12_man_id,【34】procedure12_startdate ,【35】procedure12_finishdate
# 【36】jhwg， 【37】planlog_id
# 1、核心排序：按工单的工序排列，不同工序间要加了调整时间，这种排程不用考虑工序的先后顺序，因为本身就是按时间先后顺序排列的
# type排序类型：1、按预计开工排序和计划完工日排程 2、规格相同，再按預計開工排程
def KernelArrangeWorkorder(startTime, AllJTWorkingArray, AllPlanlogResult, type):
    # 按预计开工排序和计划完工日排程
    if type == 1:   # 默认就是按预计开工日排序
        # sorted 不能比 预计开工日 作为主键
        pass
    else:
        AllPlanlogResult = sorted(AllPlanlogResult, key=lambda x: (x[2], x[7]))

    AllJTWorkingArray_copy = AllJTWorkingArray # 该数值外面还要用，不要改变
    try:
        AllUpdatePlanlogList = []
        for row_firstLoop in AllPlanlogResult:  # 第1层 一行就是一个工单
            # 定义类信息，要更新planlog表的类
            OneUpdatePlanlogList = []
            # 初始化
            # 9 ~ 20: PL.`procedure1_man_id` ~ PL.`procedure12_man_id`
            # 21 ~ 32: PL.`procedure1_startdate` ~ PL.`procedure12_startdate`
            # 33 ~ 44：PL.`procedure1_finishdate` ~ PL.`procedure12_finishdate`
            for i in range(0,12):
                OneUpdatePlanlogList.append(row_firstLoop[9 + i])
                OneUpdatePlanlogList.append(row_firstLoop[21 + i])
                OneUpdatePlanlogList.append(row_firstLoop[33 + i])

            # 一个工单一条记录
            # 1、《《《AllPlanlogResult》》》表，是planlog和workorder表合成出来的列表
            # 0: PL.`id`, 1:PL.`workorder_id`, 2:WO.`productspec_id`, 3:WO.`name`, 4:WO.`num`, 5:PL.`finishnum`, 6:PL.`maxnum`,
            # 7：PL.`yjkg`, 8：WO.`ordernumber`" \
            # 9 ~ 20: PL.`procedure1_man_id` ~ PL.`procedure12_man_id`
            # 21 ~ 32: PL.`procedure1_startdate` ~ PL.`procedure12_startdate`
            # 33 ~ 44：PL.`procedure1_finishdate` ~ PL.`procedure12_finishdate`
            # 45 ~ 56：PL.`procedure1_manhours` ~ PL.`procedure12_manhours`
            # 57 ~ 68：PL.`procedure1_id` ~ PL.`procedure12_id`
            # 69 ~ 80：PL.`procedure1_mannum` ~ PL.`procedure12_mannum`
            # 对应每个要处理的制程的要开工的时间点
            CurrentProcedureX_Startdatetime = startTime
            CurrentProcedureX_Finishdatetime = CurrentProcedureX_Startdatetime
            nWhichProcedure = row_firstLoop[5]  # 从完成的工序数开始
            # 3、《《《AllUpdatePlanlogList》》》列表元素，用于更新数据库fa_wu_planlog/planlog0
            # 【0】procedure1_man_id,【1】procedure1_startdate ,【2】procedure11_finishdate
            # 【3】procedure2_man_id,【4】procedure2_startdate ,【5】procedure2_finishdate
            # ...
            # 【33】procedure12_man_id,【34】procedure12_startdate ,【35】procedure12_finishdate
            # 【36】jhwg， 【37】planlog_id
            # 从完成工序开始，到最大的工序
            for nWhichProcedure in range(row_firstLoop[5], row_firstLoop[6]):
                # 先判断当前是不是正在做，如果是，就从下一个工序开始
                if(row_firstLoop[21 + nWhichProcedure] != None and row_firstLoop[33 + nWhichProcedure] != None):
                    if(row_firstLoop[21 + nWhichProcedure] != '0000-00-00 00:00:00' and
                            row_firstLoop[33 + nWhichProcedure] != '0000-00-00 00:00:00'):
                        if (startTime >= row_firstLoop[21 + nWhichProcedure] and startTime <= row_firstLoop[33 + nWhichProcedure]):
                            continue
                manhours = 0.0
                if(g_AllNeedPlanProcedureID2manhours_dict.has_key(row_firstLoop[57 + nWhichProcedure])):
                    # 再判断与
                    manhours = g_AllNeedPlanProcedureID2manhours_dict[row_firstLoop[57 + nWhichProcedure]]
                # 判断从该工序由哪个机台JT做，从什么时间开始做？manID就是JT_ID
                (manID, CurrentProcedureX_Startdatetime, CurrentProcedureX_Finishdatetime, AllJTWorkingArray_copy, productspecID) =\
                    WhoFromWhenDo_byTime( row_firstLoop[57 + nWhichProcedure],  # procedure_id
                                          CurrentProcedureX_Startdatetime,      # 预计开始时间starttime
                                          manhours, # 需要工时 + 调整工序的
                                          AllJTWorkingArray_copy)                    # 工作时间表
                if manID >= 0:
                    # 记录机台的更新信息到AllManWorkingArray_O列表中
                    if manID == 0: # manID = 0，就是不用安排机台，不耗工时
                        OneManWorkingArray = [manID, CurrentProcedureX_Startdatetime, CurrentProcedureX_Finishdatetime, -1, -1]
                    else:
                        OneManWorkingArray = [manID, CurrentProcedureX_Startdatetime, CurrentProcedureX_Finishdatetime, -1, productspecID] # 2:WO.`productspec_id`
                        AllJTWorkingArray_copy.append(OneManWorkingArray)
                        # 排序并统计到达下个休息时段的时间，如果是最后一个时间段，就是-1
                        AllJTWorkingArray_copy = GetSortedAllJTWorkingArrayAndToNextWorkFreetime(AllJTWorkingArray_copy)

                    # 准备追加到planlog表
                    # 9 ~ 20: PL.`procedure1_man_id` ~ PL.`procedure12_man_id`
                    # 21 ~ 32: PL.`procedure1_startdate` ~ PL.`procedure12_startdate`
                    # 33 ~ 44：PL.`procedure1_finishdate` ~ PL.`procedure12_finishdate`
                    OneUpdatePlanlogList[nWhichProcedure * 3] = manID   # procedureX_man_id
                    OneUpdatePlanlogList[nWhichProcedure * 3 + 1] = CurrentProcedureX_Startdatetime  # procedureX_startdate
                    OneUpdatePlanlogList[nWhichProcedure * 3 + 2] = CurrentProcedureX_Finishdatetime  # procedureX_finishdate
                    # 下一个工序从前一个工序结束时间开始
                    CurrentProcedureX_Startdatetime = CurrentProcedureX_Finishdatetime

            # 计划完工时间【40】jhwg
            OneUpdatePlanlogList.append(CurrentProcedureX_Finishdatetime)
            # 【41】planlog_id
            OneUpdatePlanlogList.append(row_firstLoop[0])

            # !!追加到列表中以备更新数据库
            AllUpdatePlanlogList.append(OneUpdatePlanlogList)

        return (AllJTWorkingArray_copy, AllUpdatePlanlogList)
    except:
        return (AllJTWorkingArray_copy, AllUpdatePlanlogList)

# 更新到数据库
def WriteDatabase(startTime):
    global g_AllNeedPlanProcedureID2manhours_dict
    global g_AllJT2ProcedureName_dict
    global g_AllValidJT_ID2ProceduretypeID_dict
    global g_AllNeedPlanProcedureAndJTInfos_tuple
    try:
        conn = pymysql.Connect(
            host=DatabaseIP,
            port=dbport,
            user=dbuser,
            passwd=dbpasswd,
            db=database,
            charset='utf8',
            local_infile=1
        )
        cursor = conn.cursor()
    except:
        print("Could not connect to MySQL server.")
        return False

    try:
        # 先检查fa_wu_procedureType里jt_num的累积和一定要小于等于fa_wu_jt里的总数，如果不，就报错
        # 同时提取机台信息字典
        sql = "SELECT `id`, `proceduretype_id` AS num FROM fa_wu_jt WHERE `state` IN ('0', '1')"
        result = cursor.execute(sql)
        if result:
            AllValid_jtInfo = cursor.fetchall()
            sql = "SELECT SUM(jt_num) AS sumnum FROM fa_wu_proceduretype WHERE `jt_num` > 0"
            result = cursor.execute(sql)
            if result:
                needJTnum = cursor.fetchone()
                if needJTnum[0] > len(AllValid_jtInfo):
                    print("The number of required machines is greater than the total number of machines, and the configuration is wrong.")
                    return False
            # 为了查找快，组织机台信息字典
            for row in AllValid_jtInfo:
                # 字典在赋值前都要容错
                if(not g_AllValidJT_ID2ProceduretypeID_dict.has_key(row[0])):
                    g_AllValidJT_ID2ProceduretypeID_dict[row[0]] = row[1]

        # 选取planlog的记录
        # ！！！！按 預計開工 和 計劃完工時間 排序，也是排程安排的时间
        sql = "SELECT PL.`id`, PL.`workorder_id`, WO.`productspec_id`, WO.`name`, WO.`num`, PL.`finishnum`, PL.`maxnum`, " \
              "PL.`yjkg`, WO.`ordernumber`, " \
              "PL.`procedure1_man_id`, PL.`procedure2_man_id`, PL.`procedure3_man_id`, PL.`procedure4_man_id`, " \
              "PL.`procedure5_man_id`, PL.`procedure6_man_id`, PL.`procedure7_man_id`, PL.`procedure8_man_id`, " \
              "PL.`procedure9_man_id`, PL.`procedure10_man_id`, PL.`procedure11_man_id`, PL.`procedure12_man_id`, " \
              "PL.`procedure1_startdate`, PL.`procedure2_startdate`, PL.`procedure3_startdate`, PL.`procedure4_startdate`," \
              "PL.`procedure5_startdate`, PL.`procedure6_startdate`, PL.`procedure7_startdate`, PL.`procedure8_startdate`," \
              "PL.`procedure9_startdate`, PL.`procedure10_startdate`, PL.`procedure11_startdate`, PL.`procedure12_startdate`," \
              "PL.`procedure1_finishdate`, PL.`procedure2_finishdate`, PL.`procedure3_finishdate`, PL.`procedure4_finishdate`," \
              "PL.`procedure5_finishdate`, PL.`procedure6_finishdate`, PL.`procedure7_finishdate`, PL.`procedure8_finishdate`, " \
              "PL.`procedure9_finishdate`, PL.`procedure10_finishdate`, PL.`procedure11_finishdate`, PL.`procedure12_finishdate`, " \
              "PL.`procedure1_manhours`, PL.`procedure2_manhours`, PL.`procedure3_manhours`, PL.`procedure4_manhours`," \
              "PL.`procedure5_manhours`, PL.`procedure6_manhours`, PL.`procedure7_manhours`, PL.`procedure8_manhours`," \
              "PL.`procedure9_manhours`, PL.`procedure10_manhours`, PL.`procedure11_manhours`, PL.`procedure12_manhours`," \
              "PL.`procedure1_id`,  PL.`procedure2_id`, PL.`procedure3_id`, PL.`procedure4_id`," \
              "PL.`procedure5_id`, PL.`procedure6_id`, PL.`procedure7_id`, PL.`procedure8_id`," \
              "PL.`procedure9_id`, PL.`procedure10_id`, PL.`procedure11_id`, PL.`procedure12_id`," \
              "PL.`procedure1_mannum`,  PL.`procedure2_mannum`, PL.`procedure3_mannum`, PL.`procedure4_mannum`," \
              "PL.`procedure5_mannum`, PL.`procedure6_mannum`, PL.`procedure7_mannum`, PL.`procedure8_mannum`," \
              "PL.`procedure9_mannum`, PL.`procedure10_mannum`, PL.`procedure11_mannum`, PL.`procedure12_mannum` " \
              "FROM fa_wu_planlog PL LEFT JOIN fa_wu_workorder WO on PL.`workorder_id` = WO.`id` " \
              "WHERE PL.`switch` ='1' AND PL.finishnum < PL.maxnum ORDER BY PL.`yjkg`, PL.`jhwg`"
        # test
        # sql = sql + " AND WO.`name` IN ('5101-202011045','5101-202011046','5101-202101029')"
        result = cursor.execute(sql)
        AllPlanlogResult = []
        if result:
            AllPlanlogResult = cursor.fetchall()
        if(len(AllPlanlogResult) <= 0):
            print("There aren't any workorder need to plan.")
            return True

        # 从AllPlanlogResult里面组合procedure_id，这些是准备排程的procedure_ID
        # 0: PL.`id`, 1:PL.`workorder_id`, 2:WO.`productspec_id`, 3:WO.`name`, 4:WO.`num`, 5:PL.`finishnum`, 6:PL.`maxnum`,
        # 7：PL.`yjkg`, 8:WO.`ordernumber`" \
        # 9 ~ 20: PL.`procedure1_man_id` ~ PL.`procedure12_man_id`
        # 21 ~ 32: PL.`procedure1_startdate` ~ PL.`procedure12_startdate`
        # 33 ~ 44：PL.`procedure1_finishdate` ~ PL.`procedure12_finishdate`
        # 45 ~ 56：PL.`procedure1_manhours` ~ PL.`procedure12_manhours`
        # 57 ~ 68：PL.`procedure1_id` ~ PL.`procedure12_id`
        # 69 ~ 80：PL.`procedure1_mannum` ~ PL.`procedure12_mannum`
        ALLNeedplan_procedureID_list = []
        for row in AllPlanlogResult:
            for i in range(row[5] + 1, row[6]):
                if row[57 + i] not in ALLNeedplan_procedureID_list:
                    ALLNeedplan_procedureID_list.append(row[57 + i])
        if len(ALLNeedplan_procedureID_list) >  0:
            allprocedureId_str = [str(i) for i in ALLNeedplan_procedureID_list]
            allprocedureId_str = ','.join(allprocedureId_str)
            sql = "SELECT a.`id` AS procedureID, c.`id` AS jtID, a.`productspec_id`, c.`proceduretype_id`, b.`procedurenumber`, " \
                  "a.`name`, a.`manhours`, a.`order`, b.`switch`, " \
                  "c.`name` AS JTName, c.`state`, c.`usehours` " \
                  "FROM fa_wu_procedure a INNER JOIN fa_wu_proceduretype b ON a.`name` = b.`name` " \
                  "INNER JOIN fa_wu_jt c ON b.`id` = c.`proceduretype_id` " \
                  "WHERE a.`id` IN (" + allprocedureId_str + ")" + " AND b.`switch` = '1'"
            result = cursor.execute(sql)
            if result:
                # g_AllNeedPlanProcedureAndJTInfos_tuple:
                # [0]procedure_id, [1]JT_id, [2]productspec_id [3]proceduretype_id [4]procedurenumber [5] procedure_name
                # [6]procedure_manhours [7]procedure_order [8] procedure_switch [9]jt_name [10]jt_state [11] jt_usehours
                g_AllNeedPlanProcedureAndJTInfos_tuple = cursor.fetchall()
                # 为了加快查找进度，存成字典
                for row in g_AllNeedPlanProcedureAndJTInfos_tuple:
                    if(not g_AllNeedPlanProcedureID2manhours_dict.has_key(row[0])):
                        g_AllNeedPlanProcedureID2manhours_dict[row[0]] = row[6]    # key:procedure_id value:manhours
                    # else:
                    #     print '重复的Procedure_id:', row[0]

                    if(not g_AllJT2ProcedureName_dict.has_key(row[1])):
                        g_AllJT2ProcedureName_dict[row[1]] = row[5]    # key:JT_id =>procedureName

        # 做初始化，取得机台被当前被占用的信息表
        # 1、按制程组成所有的机台：机台 / 开始工作时间点 / 结束工作时间点, 空闲时间，产品ID，proceduretype_id
        (AllJTWorkingArray) = GetAllJTWorkingInfoTable(startTime, AllPlanlogResult)

        # 1、核心排序：按工单的工序排列，不同工序间要加了调整时间
        (AllManWorkingArray_1, AllUpdatePlanlogList_1) = KernelArrangeWorkorder(
            startTime, AllJTWorkingArray, AllPlanlogResult, 1)
        # 保存到fa_wu_planloggroup表
        saveToPlanloggroup(AllManWorkingArray_1)

        # 2、核心排序：按工序排列，相同工序都统一安排，减少了不同工序的调整时间
        # 按产品规格，以及预计开工时间排序
        (AllManWorkingArray_2, AllUpdatePlanlogList_2) = KernelArrangeWorkorder(
            startTime, AllJTWorkingArray, AllPlanlogResult, 2)
        # 保存到fa_wu_planloggroup表
        saveToPlanloggroup(AllManWorkingArray_2)

        # 判断使用哪个AllUpdatePlanlogList? 以最早结束的时间为准
        AllManWorkingArray_1 = sorted(AllManWorkingArray_1, key=lambda x: (x[2]), reverse=True)
        AllManWorkingArray_2 = sorted(AllManWorkingArray_2, key=lambda x: (x[2]), reverse=True)
        if len(AllManWorkingArray_1) <= 0:
            # 没有需要更新的
            return True
        if AllManWorkingArray_1[0][2] < AllManWorkingArray_2[0][2]:
            AllUpdatePlanlogList = AllUpdatePlanlogList_1
            AllManWorkingArray = AllManWorkingArray_1
        else:
            AllUpdatePlanlogList = AllUpdatePlanlogList_2
            AllManWorkingArray = AllManWorkingArray_2

        AllUpdatePlanlogList = sorted(AllUpdatePlanlogList, key=lambda x: (x[26], x[27]))
        # 保存旧的planlog到planlog0
        sql = "UPDATE fa_wu_planlog0 A INNER JOIN fa_wu_planlog B ON A.`id` = B.`id` SET " \
              "A.`procedure1_man_id`=B.`procedure1_man_id`, A.`procedure2_man_id`=B.`procedure2_man_id`, " \
              "A.`procedure3_man_id`=B.`procedure3_man_id`, A.`procedure4_man_id`=B.`procedure4_man_id`, " \
              "A.`procedure5_man_id`=B.`procedure5_man_id`, A.`procedure6_man_id`=B.`procedure6_man_id`, " \
              "A.`procedure7_man_id`=B.`procedure7_man_id`, A.`procedure8_man_id`=B.`procedure8_man_id`, " \
              "A.`procedure9_man_id`=B.`procedure9_man_id`, A.`procedure10_man_id`=B.`procedure10_man_id`, " \
              "A.`procedure11_man_id`=B.`procedure11_man_id`, A.`procedure12_man_id`=B.`procedure12_man_id`, " \
              "A.`procedure1_finishdate`=B.`procedure1_finishdate`, A.`procedure2_finishdate`=B.`procedure2_finishdate`, " \
              "A.`procedure3_finishdate`=B.`procedure3_finishdate`, A.`procedure4_finishdate`=B.`procedure4_finishdate`, " \
              "A.`procedure5_finishdate`=B.`procedure5_finishdate`, A.`procedure6_finishdate`=B.`procedure6_finishdate`, " \
              "A.`procedure7_finishdate`=B.`procedure7_finishdate`, A.`procedure8_finishdate`=B.`procedure8_finishdate`, " \
              "A.`procedure9_finishdate`=B.`procedure9_finishdate`, A.`procedure10_finishdate`=B.`procedure10_finishdate`, " \
              "A.`procedure11_finishdate`=B.`procedure11_finishdate`, A.`procedure12_finishdate`=B.`procedure12_finishdate`, " \
              "A.`procedure1_startdate`=B.`procedure1_startdate`, A.`procedure2_startdate`=B.`procedure2_startdate`, " \
              "A.`procedure3_startdate`=B.`procedure3_startdate`, A.`procedure4_startdate`=B.`procedure4_startdate`, " \
              "A.`procedure5_startdate`=B.`procedure5_startdate`, A.`procedure6_startdate`=B.`procedure6_startdate`, " \
              "A.`procedure7_startdate`=B.`procedure7_startdate`, A.`procedure8_startdate`=B.`procedure8_startdate`, " \
              "A.`procedure9_startdate`=B.`procedure9_startdate`, A.`procedure10_startdate`=B.`procedure10_startdate`, " \
              "A.`procedure11_startdate`=B.`procedure11_startdate`, A.`procedure12_startdate`=B.`procedure12_startdate`, " \
              "A.`jhwg`=B.`jhwg` " \
              "WHERE A.`id` = B.`id`"
        result = cursor.execute(sql)
        conn.commit()
        if result:
            print "fa_wu_planlog0 datebase updates success!"
        # 选择planlog0最后更新的时间，然后将planlog里记录超过该时间的记录全部插入planlog0里，完成对Planlog0的全面更新
        sql = "SELECT update_time FROM fa_wu_planlog0 ORDER BY update_time DESC LIMIT 1"
        (ret, conn, cursor) = _reConn(conn, cursor)
        if (ret == False):
            return False
        result = cursor.execute(sql)
        if result:
            lastUpdatetime = cursor.fetchall()
            sql = "SELECT * FROM fa_wu_planlog WHERE update_time > '%s'" % lastUpdatetime[0]
            result = cursor.execute(sql)
            if result:
                All_info_planlog_tuple = cursor.fetchall()

                AllInsertPlanlog0Class = []     # 插入类
                for row_inner in All_info_planlog_tuple:
                    # 追加到列表中
                    AllInsertPlanlog0Class.append(row_inner)
                # 插入数据库中
                # 再插入数据库
                if len(AllInsertPlanlog0Class) > 0:
                    sql = "insert into fa_wu_planlog0(`id`,`workorder_id`,`workorder_finishdate`," \
                          "`finishnum`,`maxnum`,`sjwg`,`state`,`jhwg`,`yjkg`,`switch`,`overtime_hours`," \
                          "`procedure1_id`,`procedure2_id`,`procedure3_id`,`procedure4_id`," \
                          "`procedure5_id`,`procedure6_id`,`procedure7_id`,`procedure8_id`," \
                          "`procedure9_id`,`procedure10_id`,`procedure11_id`,`procedure12_id`," \
                          "`procedure1_man_id`,`procedure2_man_id`,`procedure3_man_id`,`procedure4_man_id`," \
                          "`procedure5_man_id`,`procedure6_man_id`,`procedure7_man_id`,`procedure8_man_id`," \
                          "`procedure9_man_id`,`procedure10_man_id`,`procedure11_man_id`,`procedure12_man_id`," \
                          "`procedure1_finishdate`,`procedure2_finishdate`,`procedure3_finishdate`,`procedure4_finishdate`," \
                          "`procedure5_finishdate`,`procedure6_finishdate`,`procedure7_finishdate`,`procedure8_finishdate`," \
                          "`procedure9_finishdate`,`procedure10_finishdate`,`procedure11_finishdate`,`procedure12_finishdate`," \
                          "`procedure1_startdate`,`procedure2_startdate`,`procedure3_startdate`,`procedure4_startdate`," \
                          "`procedure5_startdate`,`procedure6_startdate`,`procedure7_startdate`,`procedure8_startdate`," \
                          "`procedure9_startdate`,`procedure10_startdate`,`procedure11_startdate`,`procedure12_startdate`," \
                          "`procedure1_manhours`,`procedure2_manhours`,`procedure3_manhours`,`procedure4_manhours`," \
                          "`procedure5_manhours`,`procedure6_manhours`,`procedure7_manhours`,`procedure8_manhours`," \
                          "`procedure9_manhours`,`procedure10_manhours`,`procedure11_manhours`,`procedure12_manhours`," \
                          "`procedure1_preparedate`,`procedure2_preparedate`,`procedure3_preparedate`,`procedure4_preparedate`," \
                          "`procedure5_preparedate`,`procedure6_preparedate`,`procedure7_preparedate`,`procedure8_preparedate`," \
                          "`procedure9_preparedate`,`procedure10_preparedate`,`procedure11_preparedate`,`procedure12_preparedate`," \
                          "`procedure1_mannum`,`procedure2_mannum`,`procedure3_mannum`,`procedure4_mannum`," \
                          "`procedure5_mannum`,`procedure6_mannum`,`procedure7_mannum`,`procedure8_mannum`," \
                          "`procedure9_mannum`,`procedure10_mannum`,`procedure11_mannum`,`procedure12_mannum`," \
                          "`update_time`,`create_time`) " \
                          "values(%s,%s,%s," \
                          "%s,%s,%s,%s,%s,%s,%s,%s," \
                          "%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s," \
                          "%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s," \
                          "%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s," \
                          "%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s," \
                          "%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s," \
                          "%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s," \
                          "%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s," \
                          "%s,%s)"
                    result = batchUploadtoDB(sql, AllInsertPlanlog0Class, 100)
                    # result = cursor.executemany(sql, AllInsertPlanlog0Class)
                    if result:
                        print "fa_wu_planlog0 datebase insert success!"
        else:
            # 空的表
            sql = "SELECT * FROM fa_wu_planlog"
            result = cursor.execute(sql)
            if result:
                All_info_planlog_tuple = cursor.fetchall()

                AllInsertPlanlog0Class = []     # 插入类
                for row_inner in All_info_planlog_tuple:
                    # 追加到列表中
                    OneInsertPlanlog0_list = []
                    for i in range(len(row_inner)):
                        OneInsertPlanlog0_list.append(row_inner[i])
                    AllInsertPlanlog0Class.append(OneInsertPlanlog0_list)
                # 插入数据库中
                # 再插入数据库
                if len(AllInsertPlanlog0Class) > 0:
                    sql = "insert into fa_wu_planlog0(`id`,`workorder_id`,`workorder_finishdate`," \
                          "`finishnum`,`maxnum`,`sjwg`,`state`,`jhwg`,`yjkg`,`switch`,`overtime_hours`," \
                          "`procedure1_id`,`procedure2_id`,`procedure3_id`,`procedure4_id`," \
                          "`procedure5_id`,`procedure6_id`,`procedure7_id`,`procedure8_id`," \
                          "`procedure9_id`,`procedure10_id`,`procedure11_id`,`procedure12_id`," \
                          "`procedure1_man_id`,`procedure2_man_id`,`procedure3_man_id`,`procedure4_man_id`," \
                          "`procedure5_man_id`,`procedure6_man_id`,`procedure7_man_id`,`procedure8_man_id`," \
                          "`procedure9_man_id`,`procedure10_man_id`,`procedure11_man_id`,`procedure12_man_id`," \
                          "`procedure1_finishdate`,`procedure2_finishdate`,`procedure3_finishdate`,`procedure4_finishdate`," \
                          "`procedure5_finishdate`,`procedure6_finishdate`,`procedure7_finishdate`,`procedure8_finishdate`," \
                          "`procedure9_finishdate`,`procedure10_finishdate`,`procedure11_finishdate`,`procedure12_finishdate`," \
                          "`procedure1_startdate`,`procedure2_startdate`,`procedure3_startdate`,`procedure4_startdate`," \
                          "`procedure5_startdate`,`procedure6_startdate`,`procedure7_startdate`,`procedure8_startdate`," \
                          "`procedure9_startdate`,`procedure10_startdate`,`procedure11_startdate`,`procedure12_startdate`," \
                          "`procedure1_manhours`,`procedure2_manhours`,`procedure3_manhours`,`procedure4_manhours`," \
                          "`procedure5_manhours`,`procedure6_manhours`,`procedure7_manhours`,`procedure8_manhours`," \
                          "`procedure9_manhours`,`procedure10_manhours`,`procedure11_manhours`,`procedure12_manhours`," \
                          "`procedure1_preparedate`,`procedure2_preparedate`,`procedure3_preparedate`,`procedure4_preparedate`," \
                          "`procedure5_preparedate`,`procedure6_preparedate`,`procedure7_preparedate`,`procedure8_preparedate`," \
                          "`procedure9_preparedate`,`procedure10_preparedate`,`procedure11_preparedate`,`procedure12_preparedate`," \
                          "`procedure1_mannum`,`procedure2_mannum`,`procedure3_mannum`,`procedure4_mannum`," \
                          "`procedure5_mannum`,`procedure6_mannum`,`procedure7_mannum`,`procedure8_mannum`," \
                          "`procedure9_mannum`,`procedure10_mannum`,`procedure11_mannum`,`procedure12_mannum`," \
                          "`update_time`,`create_time`) " \
                          "values(%s,%s,%s," \
                          "%s,%s,%s,%s,%s,%s,%s,%s," \
                          "%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s," \
                          "%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s," \
                          "%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s," \
                          "%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s," \
                          "%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s," \
                          "%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s," \
                          "%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s," \
                          "%s,%s)"
                    result = batchUploadtoDB(sql, AllInsertPlanlog0Class, 100)
                    # result = cursor.executemany(sql, AllInsertPlanlog0Class)
                    if result:
                        print "fa_wu_planlog0 datebase insert success!"

        # 1、第一个数据库：更新到fa_wu_planlog数据库
        # 【0】procedure1_man_id,【1】procedure1_startdate ,【2】procedure11_finishdate
        # 【3】procedure2_man_id,【4】procedure2_startdate ,【5】procedure2_finishdate
        # ...
        # 【33】procedure12_man_id,【34】procedure12_startdate ,【35】procedure12_finishdate
        # 【36】jhwg， 【37】planlog_id
        if len(AllUpdatePlanlogList) > 0:
            sql = 'UPDATE fa_wu_planlog SET `procedure1_man_id`=%s, `procedure1_startdate`=%s, `procedure1_finishdate`=%s, ' \
                  '`procedure2_man_id`=%s, `procedure2_startdate`=%s, `procedure2_finishdate`=%s, ' \
                  '`procedure3_man_id`=%s, `procedure3_startdate`=%s, `procedure3_finishdate`=%s,' \
                  '`procedure4_man_id`=%s, `procedure4_startdate`=%s, `procedure4_finishdate`=%s, ' \
                  '`procedure5_man_id`=%s, `procedure5_startdate`=%s, `procedure5_finishdate`=%s,' \
                  '`procedure6_man_id`=%s, `procedure6_startdate`=%s, `procedure6_finishdate`=%s, ' \
                  '`procedure7_man_id`=%s, `procedure7_startdate`=%s, `procedure7_finishdate`=%s,' \
                  '`procedure8_man_id`=%s, `procedure8_startdate`=%s, `procedure8_finishdate`=%s, ' \
                  '`procedure9_man_id`=%s, `procedure9_startdate`=%s, `procedure9_finishdate`=%s,' \
                  '`procedure10_man_id`=%s, `procedure10_startdate`=%s, `procedure10_finishdate`=%s, ' \
                  '`procedure11_man_id`=%s, `procedure11_startdate`=%s, `procedure11_finishdate`=%s,' \
                  '`procedure12_man_id`=%s, `procedure12_startdate`=%s, `procedure12_finishdate`=%s, ' \
                  '`jhwg`=%s  ' \
                  'WHERE `id` = %s'
            (ret, conn, cursor) = _reConn(conn, cursor)
            if (ret == False):
                return False
            result = batchUploadtoDB(sql, AllUpdatePlanlogList, 50)
            # result = cursor.executemany(sql, AllUpdatePlanlogList)
            if result:
                print "fa_wu_planlog datebase updates success!"

        # 最后更新机台的用时
        AllUpdateJTUsehours = updateJTUsemanhours(AllJTWorkingArray)
        if(len(AllUpdateJTUsehours) > 0):
            sql = 'UPDATE fa_wu_jt SET `usehours`=%s ' \
                  'WHERE `id` = %s'
            (ret, conn, cursor) = _reConn(conn, cursor)
            if (ret == False):
                return False
            result = batchUploadtoDB(sql, AllUpdateJTUsehours, 50)
            if result:
                print "fa_wu_jt datebase updates success!"

        # 写入fa_wu_znpa表，name为workorder的ordernumber
        sql = "SELECT `id`,`name`,`workorder_finishdate_new`,`workorder_finishdate` FROM fa_wu_znpa"
        (ret, conn, cursor) = _reConn(conn, cursor)
        if (ret == False):
            return False
        result = cursor.execute(sql)
        AllNameznpa_tuple = ()
        AllNameznpa_list = []
        if result:
            AllNameznpa_tuple = cursor.fetchall()
            # 转成列表
            for row in AllNameznpa_tuple:
                if row[1] not in AllNameznpa_list:
                    AllNameznpa_list.append(row[1])

        # 3、《《《AllUpdatePlanlogList》》》列表元素，用于更新数据库fa_wu_planlog/planlog0
        # 【0】procedure1_man_id,【1】procedure1_startdate ,【2】procedure11_finishdate
        # 【3】procedure2_man_id,【4】procedure2_startdate ,【5】procedure2_finishdate
        # ...
        # 【33】procedure12_man_id,【34】procedure12_startdate ,【35】procedure12_finishdate
        # 【36】jhwg， 【37】planlog_id
        AllInsertZNPAClass = []
        AllUpdateZNPAClass = []
        for row_outer in AllUpdatePlanlogList:
            # 1、《《《AllPlanlogResult》》》表，是planlog和workorder表合成出来的列表
            # 0: PL.`id`, 1:PL.`workorder_id`, 2:WO.`productspec_id`, 3:WO.`name`, 4:WO.`num`, 5:PL.`finishnum`, 6:PL.`maxnum`,
            # 7：PL.`yjkg`, 8：WO.`ordernumber`" \
            # 9 ~ 20: PL.`procedure1_man_id` ~ PL.`procedure12_man_id`
            # 21 ~ 32: PL.`procedure1_startdate` ~ PL.`procedure12_startdate`
            # 33 ~ 44：PL.`procedure1_finishdate` ~ PL.`procedure12_finishdate`
            # 45 ~ 56：PL.`procedure1_manhours` ~ PL.`procedure12_manhours`
            # 57 ~ 68：PL.`procedure1_id` ~ PL.`procedure12_id`
            # 69 ~ 80：PL.`procedure1_mannum` ~ PL.`procedure12_mannum`
            for row_inner in AllPlanlogResult:
                if row_inner[6] == 0:  # 容错
                    continue
                if row_outer[37] == row_inner[0]:   # planlog_id
                    ordernumber = row_inner[8]  # 订单编号，是唯一的，工单名称可能不唯一
                    if row_inner[3] == None: ordernumber = ""
                    if ordernumber not in AllNameznpa_list: # 插入
                        # OneInsertZNPAClass: [0]name [1]workorder_finishdate_new [2]workorder_finishdate
                        # row_inner[5]是maxnum
                        # 插入 原来的finishdate时间设置为NULL
                        OneInsertZNPAClass = [ ordernumber,    # 订单编号
                                               string_toDatetime(row_outer[3*(row_inner[6]-1)+2]),  # workorder_finishdate_new
                                               None    # workorder_finishdate
                                               ]
                        # 添加到列表中
                        AllInsertZNPAClass.append(OneInsertZNPAClass)
                    else:   # 更新
                        # 更新的，原来的workorder_finishdate就是以前的workorder_finishdate_new
                        index_znpaName = AllNameznpa_list.index(ordernumber)
                        OneUpdateZNPAClass = [ string_toDatetime(row_outer[3*(row_inner[6]-1)+2]),  # workorder_finishdate_new
                                               AllNameznpa_tuple[index_znpaName][2],  # workorder_finishdate_new ==> workorder_finishdate
                                               AllNameznpa_tuple[index_znpaName][0] # ID
                                               ]
                        # 追加
                        AllUpdateZNPAClass.append(OneUpdateZNPAClass)
                    break

        if len(AllInsertZNPAClass) > 0:
            sql = "insert into fa_wu_znpa(`name`,`workorder_finishdate_new`,`workorder_finishdate`) " \
                  "values(%s,%s,%s)"
            result = batchUploadtoDB(sql, AllInsertZNPAClass, 100)
            if result:
                print "fa_wu_znpa datebase inserts success!"

        if len(AllUpdateZNPAClass) > 0:
            sql = "UPDATE fa_wu_znpa SET `workorder_finishdate_new`=%s,`workorder_finishdate`=%s WHERE `id` = %s"
            (ret, conn, cursor) = _reConn(conn, cursor)
            if (ret == False):
                return False
            result = batchUploadtoDB(sql, AllUpdateZNPAClass, 100)
            # result = cursor.executemany(sql, AllUpdateZNPAClass)
            if result:
                print "fa_wu_znpa datebase updates success!"

        # 写入到fs_wu_ygyg数据库
        if not WriteToYGYGDatabase(startTime, AllManWorkingArray, needJTnum[0]):
            print "WriteToYGYGDatabase failed!"

        # 数据库全部完成，一次性上传数据库
        conn.commit()
        # 关闭数据库
        cursor.close()
        conn.close()
        print "finished!"
        return True
    except:
        conn.rollback()
        cursor.close()
        conn.close()

        return False

# 保存到fa_wu_planloggroup表
def saveToPlanloggroup(AllManWorkingArray):
    if len(AllManWorkingArray) <= 0:
        return

    try:
        conn = pymysql.Connect(
            host=DatabaseIP,
            port=dbport,
            user=dbuser,
            passwd=dbpasswd,
            db=database,
            charset='utf8',
            local_infile=1
        )
        cursor = conn.cursor()
    except:
        print("Could not connect to MySQL server.")
        return False

    #组合字段
    # 3、《《《AllUpdatePlanlogList》》》列表元素，用于更新数据库fa_wu_planlog/planlog0
    # 【0】procedure1_man_id,【1】procedure1_startdate ,【2】procedure12_finishdate
    # 【3】procedure2_man_id,【4】procedure2_startdate ,【5】procedure2_finishdate
    # ...
    # 【36】procedure2_man_id,【37】procedure2_startdate ,【39】procedure2_finishdate
    # 【40】update_time，【41】jhwg， 【42】planlog_id
    # fa_wu_planloggroup：[0] begin_time [1] end_time [2] create_time
    AllManWorkingArray_copy = sorted(AllManWorkingArray, key=lambda x: (x[1]))
    begintime = AllManWorkingArray_copy[0][1]
    AllManWorkingArray_copy = sorted(AllManWorkingArray, key=lambda x: (x[2]), reverse=True)
    endtime = AllManWorkingArray_copy[0][2]
    sql = 'INSERT INTO fa_wu_planloggroup (`begin_time`, `end_time`) VALUES (%s, %s)'
    (ret, conn, cursor) = _reConn(conn, cursor)
    if (ret == False):
        return False
    result = cursor.execute(sql, (begintime, endtime))
    if result:
        print "fa_wu_planloggroup datebase insert success!"
    # 数据库提交
    conn.commit()
    # 关闭数据库
    cursor.close()
    conn.close()

def WriteToYGYGDatabase(startTime, AllManWorkingArray, needJTnum):
    try:
        if len(AllManWorkingArray) <= 0:
            print 'no plan...'
            return

        # 没有可以安排的机台
        if needJTnum <= 0:
            print 'no JT can be arrange...'
            return

        try:
            conn = pymysql.Connect(
                host=DatabaseIP,
                port=dbport,
                user=dbuser,
                passwd=dbpasswd,
                db=database,
                charset='utf8',
                local_infile=1
            )
            cursor = conn.cursor()
        except:
            print("Could not connect to MySQL server.")
            return False

        sql = "DELETE  FROM fa_wu_ygyg"
        (ret, conn, cursor) = _reConn(conn, cursor)
        if (ret == False):
            return False
        result = cursor.execute(sql)
        conn.commit()

        # 按人员的开始和结束时间排列
        # AllManWorkingArray = sorted(AllManWorkingArray, key=lambda x: (x[0], x[1], x[2]))
        # 要存储到ygyg表的列表
        AllManWorkingArray = sorted(AllManWorkingArray, key=lambda x: (x[1], x[2]))
        # 先初始化AllYGYGClass，将所有的日期填充好
        # [0] date [1]gsb [2]sumAllManhours [3]sumMansPerDay
        # 从当前时间startTime，到最后一天结束AllManWorkingArray[len(AllManWorkingArray)-1][2]
        CurrentDay = startTime.date()    # AllManWorkingArray不可能为0，前面已经做了判断
        AllYGYGClass = []
        EndDate = AllManWorkingArray[len(AllManWorkingArray)-1][2].date()
        while(True):
            OneYGYGClass = [CurrentDay, 0, needJTnum * g_PerDayWorkingTime, needJTnum]
            AllYGYGClass.append(OneYGYGClass)
            CurrentDay = CurrentDay + datetime.timedelta(days = 1)
            if CurrentDay > EndDate:
                break
        Allworkhours = 0.0  # 某个工作日内所有的工作时长，单位是小时
        # [0] date [1]gsb [2]sumAllManhours [3]sumMansPerDay
        for row in AllYGYGClass:
            # AllManWorkingArray按row[1]排的顺序
            for row_inner in AllManWorkingArray:
                if (row[0] >= row_inner[1].date() and row[0] <= row_inner[2].date() ):
                    if row[0] != None:
                        TimeCursor = datetime.datetime.strptime(str(row[0]), '%Y-%m-%d')
                        (Allworkhours, TimeCursor) = calulateTimeSpawHours(row_inner[1], row_inner[2], TimeCursor)    # 该人在该天的工作时长
                        row[1] += Allworkhours

        # 最后写入fa_wu_ygyg数据库
        # YGYGClass：# [0] date [1]gsb [2]sumAllManhours [3]sumMansPerDay
        # 先计算gsb，前面只是分子，要除以分母，即：sumAllManhours，变成百分比，整数
        for row in AllYGYGClass:
            if row[2] > 0.0:
                row[1] = int(row[1]/float(row[2]) * 100)
                if row[1] > row[2]: # 不可能超过总工时
                    row[1] = 100

        # 统一删除[1]gsb [2]sumAllManhours [3]sumMansPerDay 都等于0的项
        i = 0
        while i < len(AllYGYGClass):
            # row_0[1] = 0  row_0[2] = 0.0 row_0[3] = 0
            if(AllYGYGClass[i][1] == 0 and AllYGYGClass[i][2] == 0.0 and AllYGYGClass[i][3] == 0):
                AllYGYGClass.pop(i)
                continue
            i += 1

        # 插入数据库
        if len(AllYGYGClass)>0:
            # int(Allworkhours / OneYGYGClass.sumAllManhours * 10000)
            sql = "INSERT INTO fa_wu_ygyg(`date`, `gsb`,`sumAllManhours`,`sumMansPerDay`) " \
                  "VALUES (%s,%s,%s,%s)"
            result = batchUploadtoDB(sql, AllYGYGClass, 200)
            # result = cursor.executemany(sql, AllInsertYGYGClass)
            if result:
                print "fa_wu_ygyg datebase insert success!"
        return True
    except:
        cursor.close()
        conn.close()
        return False
    # end of WriteToYGYGDatabase

#主入口
if __name__ == '__main__':
    # 打开数据库连接
    try:
        conn = pymysql.Connect(
            host=DatabaseIP,
            port=dbport,
            user=dbuser,
            passwd=dbpasswd,
            db=database,
            charset='utf8',
            local_infile=1
        )
    except:
        print("Could not connect to MySQL server.")
        exit(0)
    # 使用cursor()方法获取操作游标
    cursor = conn.cursor()
    # 设置链接时间time_out间隔 默认 28800 秒（即 8 小时，增大设置为1天24小时
    cursor.execute('SET SESSION WAIT_TIMEOUT = 86400')

    try:
        # 初始启动时间点
        startTime = datetime.datetime.now()
        startTime = datetime.datetime.strptime(str(startTime.date()) + g_StartWorktime, '%Y-%m-%d%H:%M')

        # 调整到工作日内
        startTime = AdjustToWorkingHours(startTime, 0, 0)  # 开始派单的初始时间，按当前的时间，调整到工作日内

        # 写数据库
        if WriteDatabase(startTime) == False:
            sys.exit()
    except:
        print "something is wrong"
        exit(0)